{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第三阶段 - 第2讲：数据准备与Pandas基础\n",
    "\n",
    "## 学习目标\n",
    "- 掌握Pandas的核心数据结构（Series和DataFrame）\n",
    "- 熟练使用各种数据读取和导出方法\n",
    "- 掌握数据查看、选择、过滤的多种方式\n",
    "- 理解Pandas与Excel的操作对应关系\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from datetime import datetime, timedelta\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# 设置显示选项\n",
    "pd.set_option('display.max_columns', None)  # 显示所有列\n",
    "pd.set_option('display.max_rows', 100)      # 最多显示100行\n",
    "pd.set_option('display.width', None)        # 自动调整宽度\n",
    "pd.set_option('display.float_format', '{:.2f}'.format)  # 浮点数格式\n",
    "\n",
    "# 中文显示配置\n",
    "plt.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "print(\"✅ 环境配置完成\")\n",
    "print(f\"Pandas版本: {pd.__version__}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、Pandas核心数据结构\n",
    "\n",
    "### 1.1 Series - 一维数据结构\n",
    "\n",
    "**定义**: Series是带标签的一维数组，可以存储任何数据类型\n",
    "\n",
    "**与Excel对比**: 类似Excel中的**一列数据**\n",
    "\n",
    "**结构**:\n",
    "```\n",
    "索引(Index)    值(Values)\n",
    "    0      →     100\n",
    "    1      →     200\n",
    "    2      →     300\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建Series的多种方式\n",
    "\n",
    "# 方式1: 从列表创建\n",
    "sales = pd.Series([1000, 1500, 1200, 1800, 2000])\n",
    "print(\"从列表创建Series:\")\n",
    "print(sales)\n",
    "print(f\"数据类型: {type(sales)}\")\n",
    "print(f\"值的类型: {sales.dtype}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方式2: 指定索引\n",
    "sales_named = pd.Series(\n",
    "    [1000, 1500, 1200, 1800, 2000],\n",
    "    index=['周一', '周二', '周三', '周四', '周五'],\n",
    "    name='销售额'\n",
    ")\n",
    "print(\"\\n带标签的Series:\")\n",
    "print(sales_named)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方式3: 从字典创建\n",
    "product_prices = pd.Series({\n",
    "    'iPhone 15': 6999,\n",
    "    'MacBook Pro': 12999,\n",
    "    'iPad Air': 4999,\n",
    "    'AirPods Pro': 1999\n",
    "})\n",
    "print(\"\\n从字典创建Series:\")\n",
    "print(product_prices)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Series的基本操作\n",
    "print(\"=== Series基本操作 ===\")\n",
    "print(f\"\\n访问单个值 - 周一的销售额: ¥{sales_named['周一']:,}\")\n",
    "print(f\"访问多个值:\\n{sales_named[['周一', '周三', '周五']]}\")\n",
    "print(f\"\\n切片操作:\\n{sales_named['周二':'周四']}\")\n",
    "print(f\"\\n条件过滤(>1500):\\n{sales_named[sales_named > 1500]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Series的统计方法\n",
    "print(\"=== Series统计方法 ===\")\n",
    "print(f\"求和: ¥{sales_named.sum():,}\")\n",
    "print(f\"均值: ¥{sales_named.mean():,.2f}\")\n",
    "print(f\"中位数: ¥{sales_named.median():,.2f}\")\n",
    "print(f\"最大值: ¥{sales_named.max():,}\")\n",
    "print(f\"最小值: ¥{sales_named.min():,}\")\n",
    "print(f\"标准差: ¥{sales_named.std():,.2f}\")\n",
    "print(f\"\\n描述性统计:\\n{sales_named.describe()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 DataFrame - 二维数据结构\n",
    "\n",
    "**定义**: DataFrame是带标签的二维表格数据结构，每列可以是不同的数据类型\n",
    "\n",
    "**与Excel对比**: 类似Excel中的**工作表(Worksheet)**\n",
    "\n",
    "**结构**:\n",
    "```\n",
    "        列名1    列名2    列名3\n",
    "索引0     A        1      True\n",
    "索引1     B        2      False\n",
    "索引2     C        3      True\n",
    "```\n",
    "\n",
    "**核心概念**:\n",
    "- **行(Rows)**: 每一条记录\n",
    "- **列(Columns)**: 每个字段/属性\n",
    "- **索引(Index)**: 行标签\n",
    "- **列名(Column Names)**: 列标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建DataFrame的多种方式\n",
    "\n",
    "# 方式1: 从字典创建（最常用）\n",
    "data = {\n",
    "    '产品': ['iPhone 15', 'MacBook Pro', 'iPad Air', 'AirPods Pro', 'Apple Watch'],\n",
    "    '类别': ['手机', '电脑', '平板', '耳机', '智能手表'],\n",
    "    '单价': [6999, 12999, 4999, 1999, 2999],\n",
    "    '库存': [50, 30, 80, 120, 60],\n",
    "    '是否促销': [True, False, True, False, True]\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "print(\"从字典创建DataFrame:\")\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方式2: 从列表的列表创建\n",
    "data_list = [\n",
    "    ['张三', 28, '北京', 8000],\n",
    "    ['李四', 35, '上海', 12000],\n",
    "    ['王五', 42, '广州', 15000],\n",
    "    ['赵六', 31, '深圳', 10000]\n",
    "]\n",
    "\n",
    "df_employees = pd.DataFrame(\n",
    "    data_list,\n",
    "    columns=['姓名', '年龄', '城市', '月薪']\n",
    ")\n",
    "print(\"\\n从列表创建DataFrame:\")\n",
    "print(df_employees)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方式3: 从字典的列表创建\n",
    "orders = [\n",
    "    {'order_id': 'O001', 'product': 'iPhone 15', 'quantity': 2, 'amount': 13998},\n",
    "    {'order_id': 'O002', 'product': 'MacBook Pro', 'quantity': 1, 'amount': 12999},\n",
    "    {'order_id': 'O003', 'product': 'iPad Air', 'quantity': 3, 'amount': 14997}\n",
    "]\n",
    "\n",
    "df_orders = pd.DataFrame(orders)\n",
    "print(\"\\n从字典列表创建DataFrame:\")\n",
    "print(df_orders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrame的基本属性\n",
    "print(\"=== DataFrame基本属性 ===\")\n",
    "print(f\"形状(行×列): {df.shape}\")\n",
    "print(f\"总元素数: {df.size}\")\n",
    "print(f\"维度: {df.ndim}\")\n",
    "print(f\"\\n列名:\\n{df.columns.tolist()}\")\n",
    "print(f\"\\n索引:\\n{df.index.tolist()}\")\n",
    "print(f\"\\n数据类型:\\n{df.dtypes}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 二、数据读取与导出\n",
    "\n",
    "### 2.1 读取CSV文件\n",
    "\n",
    "**Excel对比**: \n",
    "- Excel: 文件 → 打开 → 选择CSV文件\n",
    "- Pandas: `pd.read_csv()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建示例CSV数据\n",
    "sample_data = {\n",
    "    'date': ['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04', '2024-01-05'],\n",
    "    'product': ['iPhone', 'MacBook', 'iPad', 'iPhone', 'AirPods'],\n",
    "    'sales': [15, 8, 12, 18, 25],\n",
    "    'revenue': [104985, 103992, 59988, 125982, 49975]\n",
    "}\n",
    "df_sample = pd.DataFrame(sample_data)\n",
    "\n",
    "# 保存为CSV\n",
    "df_sample.to_csv('sample_sales.csv', index=False, encoding='utf-8')\n",
    "print(\"✅ 示例CSV文件已创建: sample_sales.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取CSV文件 - 基础用法\n",
    "df_read = pd.read_csv('sample_sales.csv')\n",
    "print(\"读取的CSV数据:\")\n",
    "print(df_read)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取CSV - 高级参数\n",
    "print(\"=== read_csv()常用参数 ===\")\n",
    "\n",
    "# 参数示例\n",
    "df_advanced = pd.read_csv(\n",
    "    'sample_sales.csv',\n",
    "    encoding='utf-8',           # 编码格式\n",
    "    parse_dates=['date'],       # 自动解析日期列\n",
    "    dtype={'sales': 'int64'},   # 指定列类型\n",
    "    # nrows=3,                  # 只读取前3行\n",
    "    # skiprows=[1, 2],          # 跳过特定行\n",
    "    # usecols=['date', 'product', 'revenue']  # 只读取指定列\n",
    ")\n",
    "\n",
    "print(\"\\n高级读取结果:\")\n",
    "print(df_advanced)\n",
    "print(f\"\\ndate列类型: {df_advanced['date'].dtype}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 读取Excel文件\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 直接双击打开\n",
    "- Pandas: `pd.read_excel()`\n",
    "\n",
    "**优势**: Pandas可以读取多个工作表、指定范围、处理合并单元格等"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建示例Excel文件\n",
    "with pd.ExcelWriter('sample_sales.xlsx', engine='openpyxl') as writer:\n",
    "    df_sample.to_excel(writer, sheet_name='销售数据', index=False)\n",
    "    df_employees.to_excel(writer, sheet_name='员工信息', index=False)\n",
    "\n",
    "print(\"✅ 示例Excel文件已创建: sample_sales.xlsx (包含2个工作表)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取Excel文件\n",
    "\n",
    "# 读取指定工作表\n",
    "df_excel = pd.read_excel('sample_sales.xlsx', sheet_name='销售数据')\n",
    "print(\"读取Excel - 销售数据工作表:\")\n",
    "print(df_excel)\n",
    "\n",
    "# 读取多个工作表\n",
    "df_dict = pd.read_excel('sample_sales.xlsx', sheet_name=['销售数据', '员工信息'])\n",
    "print(\"\\n读取的工作表:\")\n",
    "print(list(df_dict.keys()))\n",
    "print(\"\\n员工信息:\")\n",
    "print(df_dict['员工信息'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Excel高级读取参数\n",
    "print(\"=== read_excel()常用参数 ===\")\n",
    "print(\"\"\"\n",
    "sheet_name: 工作表名称或索引，可以是列表读取多个\n",
    "header: 指定哪一行作为列名（默认0）\n",
    "usecols: 读取指定列 'A:C' 或 [0, 1, 2]\n",
    "skiprows: 跳过前N行\n",
    "nrows: 读取前N行\n",
    "dtype: 指定列类型\n",
    "parse_dates: 解析日期列\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 数据导出\n",
    "\n",
    "**支持格式**: CSV, Excel, JSON, SQL, HTML等"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出为CSV\n",
    "df.to_csv(\n",
    "    'products_export.csv',\n",
    "    index=False,              # 不保存索引\n",
    "    encoding='utf-8-sig',     # UTF-8带BOM（Excel打开不乱码）\n",
    "    sep=','                   # 分隔符\n",
    ")\n",
    "print(\"✅ 已导出为CSV: products_export.csv\")\n",
    "\n",
    "# 导出为Excel\n",
    "df.to_excel(\n",
    "    'products_export.xlsx',\n",
    "    sheet_name='产品清单',\n",
    "    index=False,\n",
    "    engine='openpyxl'\n",
    ")\n",
    "print(\"✅ 已导出为Excel: products_export.xlsx\")\n",
    "\n",
    "# 导出为JSON\n",
    "df.to_json(\n",
    "    'products_export.json',\n",
    "    orient='records',         # 记录格式\n",
    "    force_ascii=False,        # 支持中文\n",
    "    indent=2                  # 美化格式\n",
    ")\n",
    "print(\"✅ 已导出为JSON: products_export.json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 三、数据查看与探索\n",
    "\n",
    "### 3.1 快速查看\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 滚动浏览、Ctrl+End查看大小\n",
    "- Pandas: `head()`, `tail()`, `sample()`, `info()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建更大的示例数据集\n",
    "np.random.seed(42)\n",
    "n = 100\n",
    "\n",
    "large_data = {\n",
    "    'order_id': [f'ORD{str(i).zfill(5)}' for i in range(1, n+1)],\n",
    "    'date': pd.date_range('2024-01-01', periods=n, freq='D'),\n",
    "    'customer': np.random.choice(['张三', '李四', '王五', '赵六', '钱七'], n),\n",
    "    'product': np.random.choice(['iPhone', 'MacBook', 'iPad', 'AirPods', 'Watch'], n),\n",
    "    'quantity': np.random.randint(1, 10, n),\n",
    "    'unit_price': np.random.choice([999, 1999, 2999, 4999, 6999, 12999], n),\n",
    "    'region': np.random.choice(['华东', '华北', '华南', '华中', '西南'], n)\n",
    "}\n",
    "\n",
    "df_large = pd.DataFrame(large_data)\n",
    "df_large['total_amount'] = df_large['quantity'] * df_large['unit_price']\n",
    "\n",
    "print(f\"创建了包含{len(df_large)}条记录的数据集\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查看前几行\n",
    "print(\"前5行数据:\")\n",
    "print(df_large.head())\n",
    "\n",
    "print(\"\\n前3行数据:\")\n",
    "print(df_large.head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查看最后几行\n",
    "print(\"后5行数据:\")\n",
    "print(df_large.tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 随机抽样查看\n",
    "print(\"随机抽取5条记录:\")\n",
    "print(df_large.sample(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据信息总览\n",
    "print(\"=== 数据信息总览 ===\")\n",
    "print(df_large.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 描述性统计\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 手动计算或使用数据分析工具\n",
    "- Pandas: `describe()` 一键生成"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数值列的描述性统计\n",
    "print(\"数值列描述性统计:\")\n",
    "print(df_large.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 包含所有列的统计(包括非数值列)\n",
    "print(\"所有列描述性统计:\")\n",
    "print(df_large.describe(include='all'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分类列统计\n",
    "print(\"=== 分类列统计 ===\")\n",
    "print(\"\\n产品分布:\")\n",
    "print(df_large['product'].value_counts())\n",
    "\n",
    "print(\"\\n地区分布:\")\n",
    "print(df_large['region'].value_counts())\n",
    "\n",
    "print(\"\\n客户分布:\")\n",
    "print(df_large['customer'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 四、数据选择与过滤\n",
    "\n",
    "### 4.1 选择列\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 点击列头选中整列\n",
    "- Pandas: `df['列名']` 或 `df[['列1', '列2']]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 选择单列（返回Series）\n",
    "print(\"选择单列 - 产品:\")\n",
    "print(df_large['product'].head())\n",
    "print(f\"类型: {type(df_large['product'])}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 选择多列（返回DataFrame）\n",
    "print(\"选择多列:\")\n",
    "selected_cols = df_large[['order_id', 'product', 'quantity', 'total_amount']]\n",
    "print(selected_cols.head())\n",
    "print(f\"类型: {type(selected_cols)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 点号访问（列名不含特殊字符时可用）\n",
    "print(\"点号访问:\")\n",
    "print(df_large.product.head())\n",
    "\n",
    "# 注意：如果列名有空格或特殊字符，必须用方括号\n",
    "# df['unit price']  ✅\n",
    "# df.unit price     ❌"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 选择行\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 点击行号选中整行\n",
    "- Pandas: `loc[]` (标签) 或 `iloc[]` (位置)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用iloc按位置选择（从0开始）\n",
    "print(\"iloc - 选择第1行（索引0）:\")\n",
    "print(df_large.iloc[0])\n",
    "\n",
    "print(\"\\niloc - 选择前3行:\")\n",
    "print(df_large.iloc[0:3])  # 0, 1, 2\n",
    "\n",
    "print(\"\\niloc - 选择第1、3、5行:\")\n",
    "print(df_large.iloc[[0, 2, 4]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用loc按标签选择\n",
    "print(\"loc - 选择索引为0的行:\")\n",
    "print(df_large.loc[0])\n",
    "\n",
    "print(\"\\nloc - 选择索引0到2的行:\")\n",
    "print(df_large.loc[0:2])  # 注意：包含结束索引！"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 同时选择行和列\n",
    "print(\"iloc - 前3行，前4列:\")\n",
    "print(df_large.iloc[0:3, 0:4])\n",
    "\n",
    "print(\"\\nloc - 前3行，指定列:\")\n",
    "print(df_large.loc[0:2, ['order_id', 'product', 'total_amount']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 条件过滤\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 数据 → 筛选 → 勾选条件\n",
    "- Pandas: 布尔索引 `df[条件]`\n",
    "\n",
    "**核心思路**: 先创建布尔Series，再用它过滤DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单条件过滤\n",
    "print(\"过滤：数量>5的订单\")\n",
    "filtered = df_large[df_large['quantity'] > 5]\n",
    "print(f\"满足条件的记录数: {len(filtered)}\")\n",
    "print(filtered.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多条件过滤 - AND (&)\n",
    "print(\"过滤：产品='iPhone' 且 数量>3\")\n",
    "filtered_and = df_large[(df_large['product'] == 'iPhone') & (df_large['quantity'] > 3)]\n",
    "print(f\"满足条件的记录数: {len(filtered_and)}\")\n",
    "print(filtered_and.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多条件过滤 - OR (|)\n",
    "print(\"过滤：产品='iPhone' 或 产品='MacBook'\")\n",
    "filtered_or = df_large[(df_large['product'] == 'iPhone') | (df_large['product'] == 'MacBook')]\n",
    "print(f\"满足条件的记录数: {len(filtered_or)}\")\n",
    "print(filtered_or.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用isin()进行多值匹配\n",
    "print(\"过滤：地区为华东或华北\")\n",
    "filtered_isin = df_large[df_large['region'].isin(['华东', '华北'])]\n",
    "print(f\"满足条件的记录数: {len(filtered_isin)}\")\n",
    "print(filtered_isin.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 字符串过滤\n",
    "print(\"过滤：产品名包含'Mac'\")\n",
    "filtered_str = df_large[df_large['product'].str.contains('Mac', na=False)]\n",
    "print(f\"满足条件的记录数: {len(filtered_str)}\")\n",
    "print(filtered_str.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 复杂条件过滤\n",
    "print(\"复杂过滤：总金额>20000 且 地区在['华东','华南'] 且 数量>=3\")\n",
    "complex_filter = df_large[\n",
    "    (df_large['total_amount'] > 20000) &\n",
    "    (df_large['region'].isin(['华东', '华南'])) &\n",
    "    (df_large['quantity'] >= 3)\n",
    "]\n",
    "print(f\"满足条件的记录数: {len(complex_filter)}\")\n",
    "print(complex_filter.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4 query()方法\n",
    "\n",
    "**优势**: 更简洁的条件表达式，类似SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用query()进行过滤\n",
    "print(\"使用query: 数量>5 且 单价>5000\")\n",
    "result = df_large.query('quantity > 5 and unit_price > 5000')\n",
    "print(f\"满足条件的记录数: {len(result)}\")\n",
    "print(result.head())\n",
    "\n",
    "print(\"\\n使用query: 产品为iPhone或MacBook\")\n",
    "result2 = df_large.query('product in [\"iPhone\", \"MacBook\"]')\n",
    "print(f\"满足条件的记录数: {len(result2)}\")\n",
    "print(result2.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 五、排序操作\n",
    "\n",
    "### 5.1 按值排序\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 数据 → 排序\n",
    "- Pandas: `sort_values()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单列排序 - 升序\n",
    "print(\"按总金额升序排序:\")\n",
    "sorted_asc = df_large.sort_values('total_amount')\n",
    "print(sorted_asc.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单列排序 - 降序\n",
    "print(\"按总金额降序排序（TOP 5）:\")\n",
    "sorted_desc = df_large.sort_values('total_amount', ascending=False)\n",
    "print(sorted_desc.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多列排序\n",
    "print(\"先按地区升序，再按总金额降序:\")\n",
    "sorted_multi = df_large.sort_values(\n",
    "    ['region', 'total_amount'],\n",
    "    ascending=[True, False]\n",
    ")\n",
    "print(sorted_multi[['region', 'product', 'total_amount']].head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.2 按索引排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按索引排序\n",
    "shuffled = df_large.sample(frac=1)  # 随机打乱\n",
    "print(\"打乱后的前5行索引:\")\n",
    "print(shuffled.index[:5].tolist())\n",
    "\n",
    "sorted_by_index = shuffled.sort_index()\n",
    "print(\"\\n按索引排序后的前5行索引:\")\n",
    "print(sorted_by_index.index[:5].tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 六、Pandas vs Excel 对照表\n",
    "\n",
    "### 常用操作对比\n",
    "\n",
    "| 操作 | Excel | Pandas |\n",
    "|------|-------|--------|\n",
    "| 查看前几行 | 滚动到顶部 | `df.head()` |\n",
    "| 查看数据信息 | 手动查看 | `df.info()`, `df.describe()` |\n",
    "| 选择列 | 点击列头 | `df['列名']` 或 `df[['列1','列2']]` |\n",
    "| 选择行 | 点击行号 | `df.iloc[0:5]` 或 `df.loc[0:4]` |\n",
    "| 筛选数据 | 数据→筛选 | `df[df['列'] > 值]` |\n",
    "| 排序 | 数据→排序 | `df.sort_values('列')` |\n",
    "| 去重 | 数据→删除重复项 | `df.drop_duplicates()` |\n",
    "| 求和 | `=SUM()` | `df['列'].sum()` |\n",
    "| 均值 | `=AVERAGE()` | `df['列'].mean()` |\n",
    "| 计数 | `=COUNT()` | `df['列'].count()` |\n",
    "| 最大值 | `=MAX()` | `df['列'].max()` |\n",
    "| 分组汇总 | 数据透视表 | `df.groupby()` |\n",
    "| 条件计数 | `=COUNTIF()` | `(df['列']>值).sum()` |\n",
    "| 添加列 | 在列末输入公式 | `df['新列'] = 表达式` |\n",
    "| 删除列 | 右键→删除 | `df.drop('列', axis=1)` |\n",
    "| 保存文件 | Ctrl+S | `df.to_csv()`, `df.to_excel()` |\n",
    "\n",
    "### Pandas的优势\n",
    "\n",
    "1. **处理大数据**: 百万行数据轻松处理\n",
    "2. **自动化**: 一次编写，重复使用\n",
    "3. **可重现**: 代码记录所有步骤\n",
    "4. **灵活性**: 复杂条件、多表关联\n",
    "5. **效率**: 批量处理多个文件\n",
    "\n",
    "### Excel的优势\n",
    "\n",
    "1. **可视化**: 直观的表格界面\n",
    "2. **易上手**: 无需编程基础\n",
    "3. **即时反馈**: 实时看到修改结果\n",
    "4. **格式丰富**: 单元格格式、条件格式\n",
    "\n",
    "### 建议\n",
    "\n",
    "- **小数据 + 一次性分析** → Excel\n",
    "- **大数据 + 重复性工作** → Pandas\n",
    "- **最佳实践**: Pandas处理 + Excel展示"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 七、综合练习\n",
    "\n",
    "### 练习1: 销售数据分析\n",
    "使用`df_large`数据集，完成以下任务:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 任务1: 找出总金额TOP 10的订单\n",
    "print(\"=== 任务1: TOP 10订单 ===\")\n",
    "top10 = df_large.nlargest(10, 'total_amount')\n",
    "print(top10[['order_id', 'product', 'quantity', 'total_amount']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 任务2: 筛选出华东地区、产品为iPhone、数量>=3的订单\n",
    "print(\"\\n=== 任务2: 条件筛选 ===\")\n",
    "filtered = df_large[\n",
    "    (df_large['region'] == '华东') &\n",
    "    (df_large['product'] == 'iPhone') &\n",
    "    (df_large['quantity'] >= 3)\n",
    "]\n",
    "print(f\"满足条件的订单数: {len(filtered)}\")\n",
    "print(filtered.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 任务3: 统计每个产品的销售数量和总金额\n",
    "print(\"\\n=== 任务3: 产品销售统计 ===\")\n",
    "product_summary = df_large.groupby('product').agg({\n",
    "    'quantity': 'sum',\n",
    "    'total_amount': 'sum',\n",
    "    'order_id': 'count'\n",
    "})\n",
    "product_summary.columns = ['总销量', '总金额', '订单数']\n",
    "product_summary = product_summary.sort_values('总金额', ascending=False)\n",
    "print(product_summary)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 任务4: 找出购买最多的客户TOP 5\n",
    "print(\"\\n=== 任务4: TOP 5客户 ===\")\n",
    "customer_summary = df_large.groupby('customer').agg({\n",
    "    'total_amount': 'sum',\n",
    "    'order_id': 'count'\n",
    "}).sort_values('total_amount', ascending=False)\n",
    "customer_summary.columns = ['总消费', '订单数']\n",
    "print(customer_summary.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 练习2: 数据处理流程\n",
    "完成一个完整的数据处理流程"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 完整流程示例\n",
    "print(\"=== 完整数据处理流程 ===\")\n",
    "\n",
    "# 1. 读取数据\n",
    "df_process = df_large.copy()\n",
    "print(f\"1. 原始数据: {len(df_process)}行\")\n",
    "\n",
    "# 2. 筛选高价值订单（>10000）\n",
    "df_process = df_process[df_process['total_amount'] > 10000]\n",
    "print(f\"2. 筛选后: {len(df_process)}行\")\n",
    "\n",
    "# 3. 只保留特定产品\n",
    "df_process = df_process[df_process['product'].isin(['iPhone', 'MacBook'])]\n",
    "print(f\"3. 产品筛选后: {len(df_process)}行\")\n",
    "\n",
    "# 4. 按总金额降序排序\n",
    "df_process = df_process.sort_values('total_amount', ascending=False)\n",
    "\n",
    "# 5. 选择需要的列\n",
    "df_final = df_process[['order_id', 'date', 'product', 'quantity', 'total_amount', 'region']]\n",
    "\n",
    "# 6. 导出结果\n",
    "df_final.to_excel('high_value_orders.xlsx', index=False)\n",
    "print(\"\\n✅ 处理完成，已导出到 high_value_orders.xlsx\")\n",
    "print(\"\\n最终结果预览:\")\n",
    "print(df_final.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 八、本讲总结\n",
    "\n",
    "### 核心知识点\n",
    "\n",
    "1. **数据结构**\n",
    "   - Series: 一维数组（类似Excel的一列）\n",
    "   - DataFrame: 二维表格（类似Excel工作表）\n",
    "\n",
    "2. **数据读取**\n",
    "   - CSV: `pd.read_csv()`\n",
    "   - Excel: `pd.read_excel()`\n",
    "   - 导出: `to_csv()`, `to_excel()`, `to_json()`\n",
    "\n",
    "3. **数据查看**\n",
    "   - 快速查看: `head()`, `tail()`, `sample()`\n",
    "   - 信息总览: `info()`, `describe()`\n",
    "   - 频数统计: `value_counts()`\n",
    "\n",
    "4. **数据选择**\n",
    "   - 选列: `df['列']`, `df[['列1','列2']]`\n",
    "   - 选行: `iloc[]` (位置), `loc[]` (标签)\n",
    "   - 过滤: 布尔索引, `query()`, `isin()`\n",
    "\n",
    "5. **数据排序**\n",
    "   - 按值: `sort_values()`\n",
    "   - 按索引: `sort_index()`\n",
    "   - 多列排序: 传入列表\n",
    "\n",
    "### 关键对比\n",
    "\n",
    "| 概念 | Pandas | Excel |\n",
    "|------|--------|-------|\n",
    "| 数据结构 | DataFrame | 工作表 |\n",
    "| 列 | Series | 列 |\n",
    "| 索引 | Index | 行号 |\n",
    "| 筛选 | 布尔索引 | 筛选器 |\n",
    "| 排序 | sort_values() | 排序 |\n",
    "| 函数 | sum(), mean() | SUM(), AVERAGE() |\n",
    "\n",
    "### 下节预告\n",
    "**第3讲: 缺失值与异常值处理**\n",
    "- 缺失值的检测与处理策略\n",
    "- 异常值的识别方法（IQR、3σ）\n",
    "- 实战案例：清洗真实脏数据\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 课后作业\n",
    "\n",
    "### 作业1: 基础操作练习\n",
    "使用生成的`df_large`数据集，完成:\n",
    "1. 筛选出\"MacBook\"产品且总金额>30000的订单\n",
    "2. 按地区分组，计算每个地区的平均订单金额\n",
    "3. 找出每个产品的最大单笔订单金额\n",
    "4. 统计每个客户的购买次数和总消费\n",
    "5. 导出结果到Excel，包含多个工作表\n",
    "\n",
    "### 作业2: Excel对比练习\n",
    "用Excel和Pandas分别完成同一个任务，记录时间和步骤差异:\n",
    "- 任务：筛选销售额>15000的记录，按地区分组求和，降序排列\n",
    "- 对比：操作步骤数、耗时、可重复性\n",
    "\n",
    "### 作业3: 综合应用\n",
    "创建一个自动化脚本:\n",
    "1. 读取3个不同的CSV文件\n",
    "2. 对每个文件进行数据清洗和筛选\n",
    "3. 合并结果\n",
    "4. 生成统计报告\n",
    "5. 导出为格式化的Excel文件"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
